﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Web;
using CWB.Common.Constant;
using CWB.Common.Logging;
using CWB.Common.Exceptions;

namespace CWB.Common.Utilities
{
    public abstract class BaseDBA
    {
        private SQLiteConnection _conn;
        private SQLiteCommand _cmd;
        private SQLiteTransaction _train;

        private static ISysLog log;

        private static string _DBPath;

        protected BaseDBA()
        {
        }

        protected void init(string className, DataBaseName dbname)
        {
            string dbName = dbname.ToString();
            _conn = getDBConnection(dbName);
            _cmd = new SQLiteCommand(_conn);

            log = SysLog.GetLogger(className);
        }

        private static SQLiteConnection getDBConnection(string dbName)
        {
            string dbFolder = HttpContext.Current.Server.MapPath(string.Concat("\\App_Data"));
            string strDbFileName = String.Format("{0}\\{1}.pg", dbFolder, dbName);

            if (File.Exists(strDbFileName))
            {
                SQLiteConnection conn = new SQLiteConnection("Data Source=" + strDbFileName);
                return conn;
            }
            else
            {
                throw new Exception(string.Format("Can't find the DB file {0} in {1}.", dbName, strDbFileName));
            }
        }

        private void CreateCommand(CommandType commandType, string commandText, IList<SQLiteParameter> paraList)
        {
            _cmd.CommandType = commandType;
            _cmd.CommandText = commandText;
            _cmd.Parameters.Clear();
            if (paraList != null)
            {
                foreach (SQLiteParameter sPara in paraList)
                {
                    _cmd.Parameters.Add(sPara);
                }
            }
        }

        private void WriteErrorLog(Exception ex)
        {
            log.Exception(string.Format("Execute DB Error: {0}", ex.Message));
        }

        protected void startTrain()
        {
            _cmd.Connection.Open();
            if (_train == null)
            {
                _train = _cmd.Connection.BeginTransaction();
            }
            else
            {
                throw new TransactionOnGoing();
            }
        }

        protected void commitTrain()
        {
            if (_train != null)
            {
                _train.Commit();
            }
        }

        protected void rollbackTrain()
        {
            if (_train != null)
            {
                _train.Rollback();
            }
        }

        ~BaseDBA()
        {
            if (_train != null)
            {
                _train.Dispose();
            }

            if (_cmd != null)
            {
                _cmd.Dispose();
            }

            if (_conn != null && _conn.State == ConnectionState.Open)
            {
                _conn.Close();
                _conn.Dispose();
            }
        }

        protected int ExecuteNonQuery(SqlCommandObj cmdobj)
        {
            int irtn = 0;
            try
            {
                CreateCommand(cmdobj.commandType, cmdobj.commandText, cmdobj.paraList);
                if (_conn.State != ConnectionState.Open)
                {
                    _conn.Open();
                }
                irtn = _cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex);
                throw ex;
            }
            return irtn;
        }

        /*
        protected List<T> ExecuteReader<T>(SqlCommandObj cmdobj, Func<SQLiteDataReader, List<T>> cbFunction)
        {
            List<T> rtnT = new List<T>() { };
            try
            {
                CreateCommand(cmdobj.commandType, cmdobj.commandText, cmdobj.paraList);
                if (_conn.State != ConnectionState.Open)
                {
                    _conn.Open();
                }
                using (SQLiteDataReader dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    rtnT = cbFunction(dr);
                }
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex);
                throw ex;
            }
            return rtnT;
        }
         * */

        protected SQLiteDataReader ExecuteReader(SqlCommandObj cmdobj)
        {
            SQLiteDataReader dr;
            try
            {
                CreateCommand(cmdobj.commandType, cmdobj.commandText, cmdobj.paraList);
                if (_conn.State != ConnectionState.Open)
                {
                    _conn.Open();
                }
                dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex);
                throw ex;
            }
            return dr;
        }

        protected int ExecuteCount(SqlCommandObj cmdobj)
        {
            int irnt = 0;
            try
            {
                CreateCommand(cmdobj.commandType, cmdobj.commandText, cmdobj.paraList);
                if (_conn.State != ConnectionState.Open)
                {
                    _conn.Open();
                }
                SQLiteDataReader dr = _cmd.ExecuteReader();
                int iFieldCount = dr.FieldCount;
                if (1 == iFieldCount)
                {
                    dr.Read();
                    irnt = dr.GetInt32(0);
                }
                else
                {
                    log.Exception(string.Format("Data return column:[{0}], not just only one.", iFieldCount));
                }
                dr.Close();
                dr.Dispose();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex);
                throw ex;
            }
            finally
            {
                if (_train == null && _conn != null && _conn.State == ConnectionState.Open)
                {
                    _conn.Close();
                    _conn.Dispose();
                }
            }
            return irnt;
        }


    }

    public class SqlCommandObj
    {
        public SqlCommandObj()
        {
            commandType = CommandType.Text;
        }
        public CommandType commandType { get; set; }
        public IList<SQLiteParameter> paraList { get; set; }
        public string commandText { get; set; }
    }
}
